-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
USE [WarehouseManagement]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		TuyenVD
-- Create date: 3/16/2011
-- Description:	This stored procedure use to insert a new Role
-- =============================================
ALTER PROCEDURE InsertRole 
	-- Add the parameters for the stored procedure here
(
@RoleName nvarchar(MAX) = null,
@RoleCode varchar(10) = null, 
@BriefDescription NVARCHAR(500),
@FullDescription NVARCHAR(MAX),
@AuthorizationIDList NVARCHAR(MAX) = NULL,
@RoleID INT = -1 OUT
)	 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	--SET NOCOUNT ON;	

	IF (NOT EXISTS(SELECT 1 FROM [Role] WHERE [RoleCode] = @RoleCode OR RoleName = @RoleName))
	BEGIN
		INSERT  INTO [Role] 
		(
				[RoleCode]
				, [RoleName]
				, [BriefDescription]
				, [FullDescription]
		)
		VALUES 
		(
			@RoleCode
			, @RoleName
			, @BriefDescription
			, @FullDescription
		)	

		SELECT @RoleID = IDENT_CURRENT('[Role]')
				
		IF (EXISTS (SELECT 1 FROM [Role] WHERE @RoleID = RoleID AND RoleCode = @RoleCode))
		BEGIN
			------------------------------
			DECLARE @XmlPointer INT
	 
			EXEC sp_xml_preparedocument @XmlPointer OUTPUT, @AuthorizationIDList

			INSERT INTO 
					DefaultRoleAuthorization(RoleID, AuthorizationID)
			SELECT 
					@RoleID
					, Data.ID
			FROM OpenXml(@XmlPointer, 'Data/Item', 2)
			WITH (ID INT) AS Data
			 
			INNER JOIN --check of the valid ID from the list
					[Authorization] a
			ON
					a.[AuthorizationID] = Data.[ID]
					
			EXEC sp_xml_removedocument @XmlPointer	
			------------------------------
		END
	END
END
GO

/*
USE WarehouseManagement
GO

EXEC InsertRole @RoleName='Worker' , @Description='The person works in the warehouse'
*/